using System;
using System.Data.OleDb;
using System.Diagnostics;

namespace DataAccessTecnoPez
{
    public class SQL : IDisposable
    {
        private OleDbConnection connection;
        private OleDbTransaction transaction;
        private bool isConnected = false;
        private string sqlString;
        private static Int64 conexionesVivas;

        public OleDbConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }
        public OleDbTransaction Transaction
        {
            get
            {

                return transaction;
            }
            set { transaction = value; }
        }
        public bool IsConnected
        {
            get { return isConnected; }
            set { isConnected = value; }
        }
        public string SqlString
        {
            get { return sqlString; }
            set
            {
                Debug.WriteLine(value); 
                sqlString = value; }
        }


        /// <summary>
        /// Constructor, setea el connectionString, Abre la conexion
        /// </summary>
        public SQL()
        {
            connection = new OleDbConnection();
            try
            {
                Connection.ConnectionString = cnn.getConectionString;
                Connection.Open();
                IsConnected = true;
                conexionesVivas++;
                if (conexionesVivas > 1)
                {
                    Debug.WriteLine(String.Format("Guarda hay muchas conexiones, ahora: {0}", conexionesVivas));
                }
            }
            catch (Exception e)
            {
                IsConnected = false;
                throw new SQLManagerException(e.Message);
            }
        }

        /// <summary>
        /// Constructor, setea el connectionString, abre la conexion
        /// </summary>
        /// <param name="connectionString"></param>
        public SQL(string connectionString)
        {
            connection = new OleDbConnection();
            try
            {
                Connection.ConnectionString = connectionString;
                Connection.Open();
                IsConnected = true;
            }
            catch (Exception e)
            {
                IsConnected = false;
                throw new SQLManagerException(e.Message);
            }
        }

        /// <summary>
        /// Cerrar coneccion
        /// </summary>
        public void Close()
        {
            try
            {
                if (IsConnected)
                {
                    Connection.Close();
                    isConnected = false;
                    conexionesVivas--;
                    if (conexionesVivas > 1)
                    {
                        Debug.WriteLine(String.Format("Guarda hay muchas conexiones: {0}", conexionesVivas));
                    }
                }

            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }

        /// <summary>
        /// Iniciar transaccion
        /// </summary>
        public void InitTransaction()
        {
            try
            {
                Transaction = Connection.BeginTransaction();
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }

        /// <summary>
        /// Confirmar transaccion
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                Transaction.Commit();
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }


        /// <summary>
        /// Cancelar transaccion
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                Transaction.Rollback();
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }

        public OleDbDataReader GetDataReader(Int32 p)
        {
            object param = p;
            return GetDataReader(param);
        }

        public OleDbDataReader GetDataReader(double p)
        {
            object param = p;
            return GetDataReader(param);
        }

        public OleDbDataReader GetDataReader(long p)
        {
            object param = p;
            return GetDataReader(param);
        }

        public OleDbDataReader GetDataReader(DateTime p)
        {
            object param = p;
            return GetDataReader(param);
        }

        /// <summary>
        /// Consulta a la Base de datos
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public OleDbDataReader GetDataReader(params object[] args)
        {
            try
            {
                int parameterName = 0;
                OleDbCommand comm = new OleDbCommand(SqlString, Connection, Transaction ?? null);
                foreach (object arg in args)
                {
                    comm.Parameters.Add(parameterName.ToString(), GetOleDbType(arg));
                    parameterName++;
                }

                parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters[parameterName.ToString()].Value = GetOleDbValue(arg);
                    parameterName++;
                }

                OleDbDataReader DR = comm.ExecuteReader();
                return DR;
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);

            }

        }

        /// <summary>
        /// Consulta a la Base de datos
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public System.Data.DataSet GetDataSet(params object[] args)
        {
            try
            {
                int parameterName = 0;
                OleDbCommand comm = new OleDbCommand(SqlString, Connection, Transaction ?? null);
                foreach (object arg in args)
                {
                    comm.Parameters.Add(parameterName.ToString(), GetOleDbType(arg));
                    parameterName++;
                }

                parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters[parameterName.ToString()].Value = GetOleDbValue(arg);
                    parameterName++;
                }
                OleDbDataAdapter DA = new OleDbDataAdapter();
                System.Data.DataSet DS = new System.Data.DataSet();

                DA.SelectCommand = comm;

                DA.Fill(DS);

                return DS;
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);

            }

        }

        /// <summary>
        /// Consulta a la Base de datos
        /// </summary>
        /// <param name="Names"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public System.Data.DataSet GetDataSet(string[] Names, params object[] args)
        {
            try
            {
                int parameterName = 0;
                OleDbCommand comm = new OleDbCommand(SqlString, Connection, Transaction ?? null);
                foreach (object arg in args)
                {
                    comm.Parameters.Add(Names[parameterName], GetOleDbType(arg));
                    parameterName++;
                }

                parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters[ Names[parameterName]].Value = GetOleDbValue(arg);
                    comm.Parameters[Names[parameterName]].IsNullable = true;
                    parameterName++;
                }

                comm.CommandType = System.Data.CommandType.StoredProcedure;

                OleDbDataAdapter DA = new OleDbDataAdapter();
                System.Data.DataSet DS = new System.Data.DataSet();

                DA.SelectCommand = comm;

                DA.Fill(DS);

                return DS;
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);

            }

        }


        /// <summary>
        /// Obtiene un valor escalar.
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public object getEscalar(params object[] args)
        {
            try
            {
                int parameterName = 0;
                OleDbCommand comm = new OleDbCommand(SqlString, Connection, Transaction ?? null);
                foreach (object arg in args)
                {
                    comm.Parameters.Add(parameterName.ToString(), GetOleDbType(arg));
                    parameterName++;
                }

                parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters[parameterName.ToString()].Value = GetOleDbValue(arg);
                    parameterName++;
                }
                object result;
                result = comm.ExecuteScalar();
                return result;

            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);

            }

        }


        /// <summary>
        ///Transaccion en la base de datos
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(params object[] args)
        {
            try
            {
                OleDbCommand comm = new OleDbCommand(SqlString, Connection, Transaction ?? null);
                int parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters.Add(parameterName.ToString(), GetOleDbType(arg));
                    parameterName++;
                }

                parameterName = 0;
                foreach (object arg in args)
                {
                    comm.Parameters[parameterName.ToString()].Value = GetOleDbValue(arg);
                    parameterName++;
                }
                return comm.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }

        /// <summary>
        /// obtiene el tipo de dato para la BD segun el tipo de dato(completar)
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        private static OleDbType GetOleDbType(Object o)
        {
            try
            {
                if (o is int || o is Int16 || o is Int32 || o is Int64)
                {
                    return OleDbType.Integer;
                }
                else if (o is String || o is char)
                {
                    return OleDbType.VarChar;
                }
                else if (o is DateTime)
                {
                    return OleDbType.Date;
                }
                else if (o is bool)
                {
                    return OleDbType.Boolean;
                }
                else if (o is double || o is decimal)
                {
                    return OleDbType.Double;
                }
                else
                {
                    return OleDbType.VarChar;
                }

            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }
        /// <summary>
        /// obtiene el valor para la BD segun el tipo de dato(completar)
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        private static object GetOleDbValue(Object o)
        {
            try
            {
                if (o == null || (o is DateTime && (DateTime)o == DateTime.MinValue))
                {
                    return DBNull.Value;
                }
                else
                {
                    return o;
                }
            }
            catch (Exception e)
            {
                throw new SQLManagerException(e.Message);
            }
        }



        #region Miembros de IDisposable

        void IDisposable.Dispose()
        {
            if (transaction != null)
            {
                Transaction.Dispose();
            }
            Close();

            if (connection != null)
            {
                Connection.Dispose();
            }
        }

        #endregion
    }
}
